USE[DeltaFlex]
GO
IF(OBJECT_ID('dbo.sp_CercarOrdreTreball')IS NOT NULL)
	DROP PROCEDURE dbo.sp_CercarOrdreTreball
GO

CREATE PROCEDURE dbo.sp_CercarOrdreTreball
	@IDORDRE smallint  = NULL,
	@IDCLIENT smallint  ,
	@NOMARXIU varchar(100) = NULL,
	@REFERENCIACLIENT varchar(100) = NULL,
	@IDTREBALL varchar(100) = NULL,
	@PEDIDOCLIENT varchar(100) = NULL
AS 
	BEGIN
	DECLARE  @S_SELECT varchar(1000);
	DECLARE  @S_WHERE varchar(1000);
	DECLARE  @S_COMMAND varchar(1000);

	SET NOCOUNT ON

	SET @S_SELECT = 'SELECT IDORDRE, IDCLIENT, NOMARXIU, REFERENCIACLIENT, IDTREBALL, PEDIDOCLIENT, DATAENTREGA';
	SET @S_SELECT = @S_SELECT + ' FROM ORDRE_TREBALL';
	
	SET @S_WHERE = ' WHERE IDCLIENT = ' + CAST(@IDCLIENT AS nvarchar(100));
	
	IF @IDORDRE IS NOT NULL
		SET @S_WHERE = @S_WHERE + ' AND IDORDRE  = ' + CAST(@IDORDRE AS varchar(100));

    IF @NOMARXIU IS NOT NULL
		SET @S_WHERE = @S_WHERE + ' AND NOMARXIU  = ''' + @NOMARXIU + '''';

    IF @REFERENCIACLIENT IS NOT NULL
		SET @S_WHERE = @S_WHERE + ' AND REFERENCIACLIENT  = ''' + @REFERENCIACLIENT + '''';

    IF @IDTREBALL IS NOT NULL
		SET @S_WHERE = @S_WHERE + ' AND IDTREBALL  = ''' + @IDTREBALL + '''';

    IF @PEDIDOCLIENT IS NOT NULL
		SET @S_WHERE = @S_WHERE + ' AND PEDIDOCLIENT  = ''' + @PEDIDOCLIENT + ''''; 
		
	
	SET @S_COMMAND = @S_SELECT + @S_WHERE;

/*
	INSERT INTO dbo.LOGS(IDLOG, SPROCEDURE, SMISSATGE,DATA)
				 VALUES (NEXT VALUE FOR SEQ_LOG, 'dbo.sp_CercarOrdreTreball', @S_COMMAND, GETDATE());
*/
	EXEC(@S_COMMAND);
	END
GO